xxxxxxxxxx​

xxxxxxxxxx<font size="+3"><strong>Visualizing Data: plotly express</strong></font>

xxxxxxxxxxThere are many ways to interact with data, and one of the most powerful modes of interaction is through **visualizations**. Visualizations show data graphically, and are useful for exploring, analyzing, and presenting datasets. We use four libraries for making visualizations: [pandas](../%40textbook/07-visualization-pandas.ipynb), [Matplotlib](../%40textbook/06-visualization-matplotlib.ipynb), plotly express, and [seaborn](../%40textbook/09-visualization-seaborn.ipynb). In this section, we'll focus on using plotly express.

xxxxxxxxxx# Scatter Plots

xxxxxxxxxxA **scatter plot** is a graph that uses dots to represent values for two different numeric variables. The position of each dot on the horizontal and vertical axis indicates values for an individual data point. Scatter plots are used to observe relationships between variables, and are especially useful if you're looking for **correlations**.

 import pandas as pd​mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")​# clean the data and drop `NaNs`mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)mexico_city1 = mexico_city1.dropna(axis=0)mexico_city1.head()

xxxxxxxxxxAfter cleaning the data, we can use plotly express to draw scatter plots by specifying the DataFrame and the interested column names.

 import plotly.express as px​fig = px.scatter(mexico_city1, x="price", y="surface_covered_in_m2")fig.show()

xxxxxxxxxx<font size="+1">Practice</font>

 fig = px.scatter(mexico_city1,x="price",y="surface_covered_in_m2")fig.show()

xxxxxxxxxx# 3D Scatter Plots

 import pandas as pdimport plotly.express as px​mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)mexico_city1 = mexico_city1.dropna(axis=0)mexico_city1[    ["First Empty", "Country", "City", "Borough", "Second Empty"]] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)mexico_city1 = mexico_city1.drop(["First Empty", "Second Empty"], axis=1)mexico_city1_subset = mexico_city1.loc[1:50]​fig = px.scatter_3d(    mexico_city1_subset,    x="Borough",    y="surface_covered_in_m2",    z="price",    symbol="property_type",    color="property_type",    labels={        "surface_covered_in_m2": "Surface Covered in m^2",        "price": "Price",        "property_type": "Property Type",    },)​fig.show()

/tmp/ipykernel_659/3122900234.py:11: FutureWarning:

In a future version of pandas all arguments of StringMethods.split except for the argument 'pat' will be keyword-only.

xxxxxxxxxxNotice that the plot is interactive: you can rotate it zoom in or out. These kinds of plots also makes outliers easier to find; here, we can see that houses have higher prices than other types of properties.

xxxxxxxxxx<font size="+1">Practice</font>

 import math​​

xxxxxxxxxx# Mapbox Scatter Plots

xxxxxxxxxxA **mapbox scatter plot** is a special kind of scatter plot that allows you to create scatter plots in two dimensions and then superimpose them on top of a map. Our `mexico-city-real-estate-1.csv` dataset is a good place to start, because it includes **location data**. After importing the dataset and removing rows with missing data, split the `lat-lon` column into two separate columns: one for `latitude` and the other for `longitude`. Then use these to make a mapbox plot. Unfortunately, at present this type of plot does not easily allow for marker shape to vary based on a column of the DataFrame.

 mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)mexico_city1 = mexico_city1.dropna(axis=0)mexico_city1[["latitude", "longitude"]] = mexico_city1["lat-lon"].str.split(    ",", 2, expand=True)mexico_city1["latitude"] = mexico_city1["latitude"].astype(float)mexico_city1["longitude"] = mexico_city1["longitude"].astype(float)fig = px.scatter_mapbox(    mexico_city1,    lat="latitude",    lon="longitude",    color="property_type",    mapbox_style="carto-positron",    labels={"property_type": "Property Type"},    title="Distribution of Property Types for Sale in Mexico City",)fig.show()

/tmp/ipykernel_659/3692783844.py:6: FutureWarning:

In a future version of pandas all arguments of StringMethods.split except for the argument 'pat' will be keyword-only.

xxxxxxxxxx<font size="+1">Practice</font>

 from math import log10​​

xxxxxxxxxx# Choropleth Maps

xxxxxxxxxxA Choropleth Map is a map composed of colored polygons, showing the variable of interest at different color depth across geographies.Plotly express has a function called `px.choropleth` that be used to plot Choropleth Map. The challenges here are getting the geometry information. There are two ways, one is to use the built-in geometries in plotly when plot US States (use the state name directly) and world countries (use ISP-3 code). Another way is to look for GeoJSON files where each location has geometry information. In the following example, we will show the plot in US States with a synthetic data set.

xxxxxxxxxx # Create Synthetic Datasetdf = pd.DataFrame.from_dict(    {"State": ["CA", "TX", "NY", "HI", "DE"], "Temparature": [100, 120, 110, 90, 105]})df

xxxxxxxxxx # Plot the data set in US mapfig = px.choropleth(    df, locations="State", locationmode="USA-states", color="Temparature", scope="usa")fig.show()

xxxxxxxxxx# Histogram

xxxxxxxxxxA **histogram** is a graph that shows the frequency distribution of numerical data. In addition to helping us understand frequency, histograms are also useful for detecting outliers. We can use the `px.histogram()` function from Plotly to draw histograms for specific columns, as long as the data type is numerical. Let's check the following example:

xxxxxxxxxx import plotly.express as px​df = pd.read_csv("data/mexico-city-real-estate-1.csv")fig = px.histogram(df, x="price")fig.show()

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx fig = px.histogram(df,x="surface_covered_in_m2")fig.show()

xxxxxxxxxx# Boxplots

xxxxxxxxxxA **boxplot** is a graph that shows the minimum, first quartile, median, third quartile, and the maximum values in a dataset. Boxplots are useful because they provide a visual summary of the data, enabling researchers to quickly identify mean values, the dispersion of the data set, and signs of skewness. In the following example, we will explore how to draw boxplots for specific columns of a DataFrame.

xxxxxxxxxx # Read Datamexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")​# Clean the data and drop `NaNs`mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)mexico_city1 = mexico_city1.dropna(axis=0)​# Exclude some outliersmexico_city1 = mexico_city1[mexico_city1["price"] < 100000000]mexico_city1.head()

xxxxxxxxxxCheck the boxplot for column `"price"`:

xxxxxxxxxx import plotly.express as px​fig = px.box(mexico_city1, y="price")fig.show()

xxxxxxxxxxIf you want to check the distribution of a column value by different categories, defined by another categorical column, you can add an `x` argument to specify the name of the categorical column. In the following example, we check the price distribution across different property types:

xxxxxxxxxx fig = px.box(mexico_city1, x="property_type", y="price")fig.show()

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx fig = ...fig.show()

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In [15], line 2
      1 fig = ...
----> 2 fig.show()

AttributeError: 'ellipsis' object has no attribute 'show'

xxxxxxxxxx# Bar Chart

xxxxxxxxxxA **bar chart** is a graph that shows all the values of a categorical variable in a dataset. They consist of an axis and a series of labeled horizontal or vertical bars. The bars depict frequencies of different values of a variable or simply the different values themselves. The numbers on the y-axis of a vertical bar chart or the x-axis of a horizontal bar chart are called the scale.

xxxxxxxxxx # Read Datamexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")​# Clean the data and drop `NaNs`mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)​# find location columns from place_with_parent_namesmexico_city1[    ["First Empty", "Country", "City", "Borough", "Second Empty"]] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)mexico_city1 = mexico_city1.drop(["First Empty", "Second Empty"], axis=1)mexico_city1 = mexico_city1.dropna(axis=0)​# Exclude some outliersmexico_city1 = mexico_city1[mexico_city1["price"] < 100000000]mexico_city1 = mexico_city1[mexico_city1["Borough"] != ""]​mexico_city1.head()

xxxxxxxxxxWe can calculate the number of real estate showing in the data set by Borough using `value_counts()`, then plot it as bar plot:

xxxxxxxxxx # Use value_counts() to get the datamexico_city1["Borough"].value_counts()

xxxxxxxxxx # Plot value_counts() datafig = px.bar(mexico_city1["Borough"].value_counts())fig.show()

xxxxxxxxxxWe can plot more expressive bar plots by adding more arguments. For example, we can plot the number of observations by borough and property type. First of all, we need use `groupby` to calculate the aggregated counts for each Borough and property type combination:

xxxxxxxxxx size_df = mexico_city1.groupby(["Borough", "property_type"], as_index=False).size()size_df.head()

xxxxxxxxxxBy specifying `x`, `y` and `color`, the following bar graph shows the total counts by Borough, with different property types showing in different colors. Note `y` has to be numerical, while `x` and `color` are usually categorical variables.<span style='color: transparent; font-size:1%'>WQU WorldQuant University Applied Data Science Lab QQQQ</span>

xxxxxxxxxx fig = px.bar(size_df, x="Borough", y="size", color="property_type", barmode="relative")fig.show()

xxxxxxxxxxNote the argument `barmode` is specified as 'relative', which is also the default value. In this mode, bars are stacked above each other. We can also use 'overlay' where bars are drawn on top of each other.

xxxxxxxxxx fig = px.bar(size_df, x="Borough", y="size", color="property_type", barmode="overlay")fig.show()

xxxxxxxxxxIf we want bars to be placed beside each other, we can specify `barmode` as "group":

xxxxxxxxxx fig = px.bar(size_df, x="Borough", y="size", color="property_type", barmode="group")fig.show()

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx bar_df = ...​fig = ...fig.show()

xxxxxxxxxx# References and Further Reading

xxxxxxxxxx---

xxxxxxxxxx​

xxxxxxxxxx<font size="+3"><strong>Visualizing Data: seaborn</strong></font>

xxxxxxxxxxThere are many ways to interact with data, and one of the most powerful modes of interaction is through **visualizations**. Visualizations show data graphically, and are useful for exploring, analyzing, and presenting datasets. We use four libraries for making visualizations: [pandas](../%40textbook/07-visualization-pandas.ipynb), [Matplotlib](../%40textbook/06-visualization-matplotlib.ipynb), [plotly express](../%40textbook/08-visualization-plotly.ipynb), and seaborn. In this section, we'll focus on using seaborn.

xxxxxxxxxx# Scatter Plots

xxxxxxxxxxA **scatter plot** is a graph that uses dots to represent values for two different numeric variables. The position of each dot on the horizontal and vertical axis indicates values for an individual data point. Scatter plots are used to observe relationships between variables, and are especially useful if you're looking for **correlations**.

 import pandas as pdimport seaborn as sns​# Read Datamexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")​# Clean the data and drop `NaNs`mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)​mexico_city1 = mexico_city1.dropna(axis=0)​# Exclude some outliersmexico_city1 = mexico_city1[mexico_city1["price"] < 100000000]​mexico_city1.head()

xxxxxxxxxxUse seaborn to plot the scatter plot for `"price"` and `"surface_covered_in_m2"`:

 sns.scatterplot(data=mexico_city1, x="price", y="surface_covered_in_m2");

xxxxxxxxxxThere is a very useful argument in `scatterplot` called `hue`. By specifying a categorical column as `hue`, seaborn can create a scatter plot between two variables in different categories with different colors. Let's check the following example using `"property_type"`:

 sns.scatterplot(    data=mexico_city1, x="price", y="surface_covered_in_m2", hue="property_type");

xxxxxxxxxx<font size="+1">Practice</font>

​

xxxxxxxxxx# Bar Charts

xxxxxxxxxxA **bar chart** is a graph that shows all the values of a categorical variable in a dataset. They consist of an axis and a series of labeled horizontal or vertical bars. The bars depict frequencies of different values of a variable or simply the different values themselves. The numbers on the y-axis of a vertical bar chart or the x-axis of a horizontal bar chart are called the scale.

xxxxxxxxxx # Read Datamexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")​# Clean the data and drop `NaNs`mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)​# find location columns from place_with_parent_namesmexico_city1[    ["First Empty", "Country", "City", "Borough", "Second Empty"]] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)mexico_city1 = mexico_city1.drop(["First Empty", "Second Empty"], axis=1)mexico_city1 = mexico_city1.dropna(axis=0)​# Exclude some outliersmexico_city1 = mexico_city1[mexico_city1["price"] < 100000000]mexico_city1 = mexico_city1[mexico_city1["Borough"] != ""]​mexico_city1.head()

/tmp/ipykernel_721/836102575.py:12: FutureWarning: In a future version of pandas all arguments of StringMethods.split except for the argument 'pat' will be keyword-only.
  ] = mexico_city1["place_with_parent_names"].str.split("|", 4, expand=True)

xxxxxxxxxxLet's check the example of a bar plot showing the value counts of each borough in the dataset. We first need to create a DataFrame showing the value counts:

 bar_df = pd.DataFrame(mexico_city1["Borough"].value_counts()).reset_index()bar_df

xxxxxxxxxxSince there are 16 different categories in Borough, we should increase the default plot size and rotate the x axis to make the plot more readable using the following syntax:

 # Increase plot sizesns.set(rc={"figure.figsize": (15, 4)})​# Plot the bar plotax = sns.barplot(data=bar_df, x="index", y="Borough")​# Rotate the x axisax.set_xticklabels(ax.get_xticklabels(), rotation=75)

[Text(0, 0, 'Miguel Hidalgo'),
 Text(1, 0, 'Cuajimalpa de Morelos'),
 Text(2, 0, 'Álvaro Obregón'),
 Text(3, 0, 'Benito Juárez'),
 Text(4, 0, 'Tlalpan'),
 Text(5, 0, 'Iztapalapa'),
 Text(6, 0, 'Tláhuac'),
 Text(7, 0, 'Cuauhtémoc'),
 Text(8, 0, 'Gustavo A. Madero'),
 Text(9, 0, 'Venustiano Carranza'),
 Text(10, 0, 'Coyoacán'),
 Text(11, 0, 'La Magdalena Contreras'),
 Text(12, 0, 'Xochimilco'),
 Text(13, 0, 'Iztacalco'),
 Text(14, 0, 'Azcapotzalco'),
 Text(15, 0, 'Milpa Alta')]

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx pro_typ_df = pd.DataFrame(mexico_city1["property_type"].value_counts()).reset_index()pro_typ_df​sns.barplot(data =pro_typ_df,x="index",y="property_type")​

<AxesSubplot:xlabel='index', ylabel='property_type'>

xxxxxxxxxx# Correlation Heatmaps

xxxxxxxxxx import pandas as pdimport seaborn as sns​mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")mexico_city1 = mexico_city1.drop(    ["floor", "price_usd_per_m2", "expenses", "rooms"], axis=1)mexico_city1 = mexico_city1.dropna(axis=0)mexico_city1_numeric = mexico_city1.select_dtypes(include="number")corr = mexico_city1_numeric.corr(method="kendall")sns.heatmap(corr)

<AxesSubplot:>

xxxxxxxxxxNotice that we dropped the columns and rows with missing entries before plotting the graph.

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx ​

xxxxxxxxxx# References and Further Reading

xxxxxxxxxx---

xxxxxxxxxx---

xxxxxxxxxx​

xxxxxxxxxx<font size="+3"><strong>Databases: SQL</strong></font>

 from IPython.display import YouTubeVideo

xxxxxxxxxx# Working with SQL Databases

xxxxxxxxxxA database is a collection of interrelated data. The primary goal of a database is to store and retrieve information in a convenient and efficient way. There are many types of databases. In this section, we will be dealing with a **relational database**. A relational database is a widely used database model that consists of a collection of uniquely named **tables** used to store information. The structure of a database model with its tables, constraints, and relationships is called a **schema**.

xxxxxxxxxx# Connecting to a Database

xxxxxxxxxx## ipython-sql

xxxxxxxxxx### Magic Commands

xxxxxxxxxxJupyter notebooks can run code that is not valid Python code but still affect the notebook . These special commands are called magic commands. Magic commands can have a range of properties. Some commonly used magic functions are below:

xxxxxxxxxx### ipython-sql

xxxxxxxxxx`ipython-sql` allows you to write SQL code directly in a Jupyter Notebook. The `%sql` (or `%%sql`) magic command is added to the beginning of a code block and then SQL code can be written.

xxxxxxxxxx### Connecting with ipython-sql

xxxxxxxxxxWe can connect to a database using the %sql magic function:

 %load_ext sql%sql sqlite:////home/jovyan/nepal.sqlite

xxxxxxxxxx## sqlite3

xxxxxxxxxxWe can also connect to the same database using the sqlite3 package:

 import sqlite3​conn = sqlite3.connect("/home/jovyan/nepal.sqlite")

xxxxxxxxxx# Querying a Database

xxxxxxxxxx## Building Blocks of the Basic Query

xxxxxxxxxxThere are six common clauses used for querying data:

xxxxxxxxxx## SELECT and FROM

xxxxxxxxxxYou can use `SELECT *` to select all columns in a table. `FROM` specifies the table in the database to query. `LIMIT 5` will select only the first five rows.

xxxxxxxxxx %%sql​SELECT *FROM id_mapLIMIT 5

xxxxxxxxxxYou can also use `SELECT` to select certain columns in a table

xxxxxxxxxx %%sql​SELECT household_id,       building_idFROM id_mapLIMIT 5

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx %%sql​​

xxxxxxxxxxWe can also assign an **alias** or temporary name to a column using the `AS` command. Aliases can also be used on a table. See the example below, which assigns the alias `household_number` to `household_id`

xxxxxxxxxx %%sql​SELECT household_id AS household_number,       building_idFROM id_mapLIMIT 5

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx %%sql​​

xxxxxxxxxx## Filtering and Sorting Data

xxxxxxxxxxSQL provides a variety of comparison operators that can be used with the WHERE clause to filter the data.

xxxxxxxxxxFor example, to select the first 5 homes in Ramechhap (district `2`):

xxxxxxxxxx %%sql​​

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx %%sql​​

xxxxxxxxxx## Aggregating Data

xxxxxxxxxxAggregation functions take a collection of values as inputs and return one value as the output. The table below gives the frequently used built-in aggregation functions:

xxxxxxxxxxUse the `COUNT` function to find the number of observations in the `id_map` table that come from Ramechhap (district `2`):

xxxxxxxxxx %%sql​SELECT count(*)FROM id_mapWHERE district_id = 2

xxxxxxxxxxAggregation functions are frequently used with a `GROUP BY` clause to perform the aggregation on groups of data. For example, the query below returns the count of observations in each District:

xxxxxxxxxx %%sql​SELECT district_id,       count(*)FROM id_mapGROUP BY district_id

xxxxxxxxxx `DISTINCT` is a keyword to select unique records in a query result. For example, if we want to know the unique values in the `district_id` column:

xxxxxxxxxx %%sql​SELECT distinct(district_id)FROM id_map

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx %%sql​​

xxxxxxxxxx`DISTINCT` and `COUNT` can be used in combination to count the number of distinct records. For example, if we want to know the number of unique values in the `district_id` column:

xxxxxxxxxx %%sql​SELECT count(distinct(district_id))FROM id_map

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx %%sql​​

xxxxxxxxxx# Joining Tables

xxxxxxxxxxJoins link data from two or more tables together by using a column that is common between the two tables. The basic syntax for a join is below, where `table1` and `table2` refer to the two tables being joined, `column1` and `column2` refer to columns to be returned from both tables, and `ID` refers to the common column in the two tables.

xxxxxxxxxxWe'll explore the concept of joins by first identifying a single household that we'd like to pull in building information for. For example, let's say we want to see the corresponding `foundation_type` for the first home in Ramechhap (District 1). We'll start by looking at this single record in the `id_map` table.

xxxxxxxxxx %%sql​SELECT *FROM id_mapWHERE district_id = 2LIMIT 1

xxxxxxxxxxThis household has `building_id` equal to 23. Let's look at the `foundation_type` for this building, by filtering the `building_structure` table to find this building.

xxxxxxxxxx %%sql​SELECT building_id,       foundation_typeFROM building_structureWHERE building_id = 23

xxxxxxxxxxTo join the two tables and limit the results to `building_id = 23`:

xxxxxxxxxx %%sql​SELECT id_map.*,       building_structure.foundation_typeFROM id_mapJOIN building_structure ON id_map.building_id = building_structure.building_idWHERE id_map.building_id = 23

xxxxxxxxxxIn addition to the basic `JOIN` clause, specific join types can be specified, which specify whether the common column needs to be in one, both, or either of the two tables being joined. The different join types are below. The left table is the table specified first, immediately after the `FROM` clause and the right table is the table specified after the `JOIN` clause. If the generic `JOIN` clause is used, then by default the `INNER JOIN` will be used.

xxxxxxxxxxThe video below outlines the main types of joins:

xxxxxxxxxx YouTubeVideo("2HVMiPPuPIM")

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx %%sql​​

xxxxxxxxxx# Using pandas with SQL Databases

xxxxxxxxxxTo save the output of a query into a pandas DataFrame, we will use connect to the SQLite database using the SQLite3 package:

xxxxxxxxxx import sqlite3​conn = sqlite3.connect("/home/jovyan/nepal.sqlite")

xxxxxxxxxxTo run a query using `sqlite3`, we need to store the query as a string. For example, the variable below called `query` is a string containing a query which returns the first 10 rows from the `id_map` table:

xxxxxxxxxx query = """    SELECT *    FROM id_map    LIMIT 10    """

xxxxxxxxxxTo save the results of the query into a pandas DataFrame, use the `pd.read_sql()` function. The optional parameter `index_col` can be used to set the index to a specific column from the query.

xxxxxxxxxx import pandas as pd​df = pd.read_sql(query, conn, index_col="building_id")​df.head()

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx query = ...​df2 = ...​df2.head()

xxxxxxxxxx# References & Further Reading

xxxxxxxxxx---

xxxxxxxxxx​

xxxxxxxxxx<font size="+3"><strong>Databases: PyMongo</strong></font>

xxxxxxxxxx# Working with PyMongo

 from pprint import PrettyPrinter​import pandas as pdfrom pymongo import MongoClient

xxxxxxxxxx## Databases

xxxxxxxxxx## Servers and Clients

 pp = PrettyPrinter(indent=2)client = MongoClient(host="localhost", port=27017)

xxxxxxxxxx## Semi-structured Data

xxxxxxxxxx## Exploring a Database

 pp.pprint(list(client.list_databases()))

[ {'empty': False, 'name': 'admin', 'sizeOnDisk': 40960},
  {'empty': False, 'name': 'air-quality', 'sizeOnDisk': 7000064},
  {'empty': False, 'name': 'config', 'sizeOnDisk': 12288},
  {'empty': False, 'name': 'local', 'sizeOnDisk': 73728},
  {'empty': False, 'name': 'wqu-abtest', 'sizeOnDisk': 585728}]

xxxxxxxxxxIt looks like this server contains four databases: `"admin"`, `"air-quality"`, `"config"`, and `"local"`. We're only interested in `"air-quality"`, so let's connect to that one:

 db = client["air-quality"]

xxxxxxxxxxIn MongoDB, a **database** is a container for **collections**. Each database gets its own set of files, and a single MongoDB **server** typically has multiple databases.

xxxxxxxxxx## Collections

 for c in db.list_collections():    print(c["name"])

system.views
lagos
system.buckets.lagos
nairobi
system.buckets.nairobi
dar-es-salaam
system.buckets.dar-es-salaam

xxxxxxxxxxAs you can see, there are three actual collections here: `"nairobi"`, `"lagos"`, and `"dar-es-salaam"`. Since we're only interested in the `"lagos"` collection, let's get it on its own like this:

 lagos = db["lagos"]

xxxxxxxxxx## Documents

xxxxxxxxxxA MongoDB **document** is an individual record of data in a **collection**, and is the basic unit of analysis in MongoDB. Documents come with **metadata** that helps us understand what the document is; we'll get back to that in a minute. In the meantime, let's use the [`count_documents`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.count_documents) method to see how many documents the `"lagos"` collection contains:

 lagos.count_documents({})

166496

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx from pymongo import MongoClientfrom pprint import PrettyPrinter​client = MongoClient(host = "localhost", port = 27017)pp = PrettyPrinter(indent = 5)​pp.pprint(list(client.list_databases()))db = client["air-quality"]for c in db.list_collections():    print(c["name"])    nairobi = db["nairobi"]nairobi.count_documents({})​

[    {'empty': False, 'name': 'admin', 'sizeOnDisk': 40960},
     {'empty': False, 'name': 'air-quality', 'sizeOnDisk': 7000064},
     {'empty': False, 'name': 'config', 'sizeOnDisk': 12288},
     {'empty': False, 'name': 'local', 'sizeOnDisk': 73728},
     {'empty': False, 'name': 'wqu-abtest', 'sizeOnDisk': 585728}]
system.views
lagos
system.buckets.lagos
nairobi
system.buckets.nairobi
dar-es-salaam
system.buckets.dar-es-salaam

202212

xxxxxxxxxx### Retrieving Data

xxxxxxxxxx result = lagos.find_one({})pp.pprint(result)

{    '_id': ObjectId('6334b0f18c51459f9b1d955d'),
     'metadata': {    'lat': 6.501,
                      'lon': 3.367,
                      'measurement': 'temperature',
                      'sensor_id': 10,
                      'sensor_type': 'DHT11',
                      'site': 4},
     'temperature': nan,
     'timestamp': datetime.datetime(2018, 1, 7, 7, 7, 3, 88000)}

xxxxxxxxxx### Key-Value Pairs

xxxxxxxxxx### Metadata

xxxxxxxxxx### Identifiers

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx result = nairobi.find_one({})pp.pprint(result)

{    'P1': 39.67,
     '_id': ObjectId('6334b0e98c51459f9b198d27'),
     'metadata': {    'lat': -1.3,
                      'lon': 36.785,
                      'measurement': 'P1',
                      'sensor_id': 57,
                      'sensor_type': 'SDS011',
                      'site': 29},
     'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}

xxxxxxxxxx## Analyzing Data

xxxxxxxxxx lagos.distinct("metadata.site")

[3, 4]

xxxxxxxxxxNotice that in order to grab the `"site"` number, we needed to include the `"metadata"` tag.

xxxxxxxxxx print("Documents from site 3:", lagos.count_documents({"metadata.site": 3}))print("Documents from site 4:", lagos.count_documents({"metadata.site": 4}))

Documents from site 3: 140586
Documents from site 4: 25910

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx nairobi.distinct("metadata.site")print("Documents from site 29:", nairobi.count_documents({"metadata.site":29}))print("Documents from site 6:", nairobi.count_documents({"metadata.site":6}))

Documents from site 29: 131852
Documents from site 6: 70360

xxxxxxxxxx print("Documents from site 29:", nairobi.count_documents({"metadata.site": 29}))print("Documents from site 6:", nairobi.count_documents({"metadata.site": 6}))# REMOVE}

Documents from site 29: 131852
Documents from site 6: 70360

xxxxxxxxxxNow that we know how many *documents* are associated with each site, let's keep drilling down and find the number of *readings* for each site. We'll do this with the [`aggregate`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.aggregate) method.

xxxxxxxxxx result = lagos.aggregate(    # Here's the `$` and the `"_id"`    [{"$group": {"_id": "$metadata.site", "count": {"$count": {}}}}])pp.pprint(list(result))

[{'_id': 3, 'count': 140586}, {'_id': 4, 'count': 25910}]

xxxxxxxxxxWith that information in mind, we might want to know what those readings actually are. Since we're really interested in measures of air quality, let's take a look at the `P2` values in the `"lagos"` collection. `P2` measures the amount of particulate matter in the air, which in this case is something called PM 2.5. If we wanted to get all the documents in a collection, we could, but that would result in an unmanageably large number of records clogging up the memory on our machines. Instead, let's use the [`find`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.find) method and use `limit` to make sure we only get back the first 3.

xxxxxxxxxx result = lagos.find({"metadata.measurement": "P2"}).limit(3)pp.pprint(list(result))

[    {    'P2': 14.42,
          '_id': ObjectId('6334b0f28c51459f9b1de145'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 7, 3, 39000)},
     {    'P2': 19.66,
          '_id': ObjectId('6334b0f28c51459f9b1de146'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 11, 23, 870000)},
     {    'P2': 24.79,
          '_id': ObjectId('6334b0f28c51459f9b1de147'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 21, 53, 981000)}]

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx result = nairobi.aggregate(    # Here's the `$` and the `"_id"`    [{"$group": {"_id": "$metadata.site", "count": {"$count": {}}}}])pp.pprint(list(result))result = lagos.find({"metadata.measurement": "P2"}).limit(3)pp.pprint(list(result))

[{'_id': 6, 'count': 70360}, {'_id': 29, 'count': 131852}]
[    {    'P2': 14.42,
          '_id': ObjectId('6334b0f28c51459f9b1de145'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 7, 3, 39000)},
     {    'P2': 19.66,
          '_id': ObjectId('6334b0f28c51459f9b1de146'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 11, 23, 870000)},
     {    'P2': 24.79,
          '_id': ObjectId('6334b0f28c51459f9b1de147'),
          'metadata': {    'lat': 6.501,
                           'lon': 3.367,
                           'measurement': 'P2',
                           'sensor_id': 6,
                           'sensor_type': 'PPD42NS',
                           'site': 4},
          'timestamp': datetime.datetime(2018, 1, 7, 7, 21, 53, 981000)}]

xxxxxxxxxxSo far, we've been dealing with relatively small subsets of the data in our collections, but what if we need to work with something bigger? Let's start by using `distinct` to remind ourselves of the kinds of data we have at our disposal.

xxxxxxxxxx lagos.distinct("metadata.measurement")

['humidity', 'temperature', 'P1', 'P2']

xxxxxxxxxxThere are also comparison query operators that can be helpful for filtering the data. In total, we have

xxxxxxxxxx import datetime​result = nairobi.find({"timestamp": {"$gt": datetime.datetime(2018, 9, 1)}}).limit(3)pp.pprint(list(result))

[    {    'P1': 39.67,
          '_id': ObjectId('6334b0e98c51459f9b198d27'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)},
     {    'P1': 39.13,
          '_id': ObjectId('6334b0e98c51459f9b198d28'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 5, 3, 941000)},
     {    'P1': 30.07,
          '_id': ObjectId('6334b0e98c51459f9b198d29'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 10, 4, 374000)}]

xxxxxxxxxx result = nairobi.find({"timestamp": {"$lt": datetime.datetime(2018, 12, 1)}}).limit(3)pp.pprint(list(result))

[    {    'P1': 39.67,
          '_id': ObjectId('6334b0e98c51459f9b198d27'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)},
     {    'P1': 39.13,
          '_id': ObjectId('6334b0e98c51459f9b198d28'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 5, 3, 941000)},
     {    'P1': 30.07,
          '_id': ObjectId('6334b0e98c51459f9b198d29'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 10, 4, 374000)}]

xxxxxxxxxx result = nairobi.find(    {"timestamp": {"$eq": datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}}).limit(3)pp.pprint(list(result))

[    {    'P1': 39.67,
          '_id': ObjectId('6334b0e98c51459f9b198d27'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)},
     {    'P2': 34.43,
          '_id': ObjectId('6334b0ea8c51459f9b1a0db2'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P2',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}]

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx # Greater than or equal to​result = nairobi.find({"timestamp":{"$gte":datetime.datetime(2018,12,1,0,0,6,767000)}}).limit(3)​pp.pprint(list(result))

[    {    'P1': 17.08,
          '_id': ObjectId('6334b0e98c51459f9b19eba8'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 12, 1, 0, 0, 6, 767000)},
     {    'P1': 17.62,
          '_id': ObjectId('6334b0e98c51459f9b19eba9'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 12, 1, 0, 5, 6, 327000)},
     {    'P1': 11.05,
          '_id': ObjectId('6334b0e98c51459f9b19ebaa'),
          'metadata': {    'lat': -1.3,
                           'lon': 36.785,
                           'measurement': 'P1',
                           'sensor_id': 57,
                           'sensor_type': 'SDS011',
                           'site': 29},
          'timestamp': datetime.datetime(2018, 12, 1, 0, 10, 5, 579000)}]

xxxxxxxxxx # Less than or equal to​result = ...​pp.pprint(list(result))

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In [23], line 5
      1 # Less than or equal to
      3 result = ...
----> 5 pp.pprint(list(result))

TypeError: 'ellipsis' object is not iterable

xxxxxxxxxx## Updating Documents

xxxxxxxxxxWe can also update documents by passing some filter and new values using `update_one` to update one record or `update_many` to update many records. Let's look at an example. Before updating, we have this record showing like this:

xxxxxxxxxx result = nairobi.find(    {"timestamp": {"$eq": datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}}).limit(1)​pp.pprint(list(result))

xxxxxxxxxxNow we are updating the sensor type from `"SDS011"` to `"SDS"`, we first select all records with sensor type equal to `"SDS011"`, then set the new value to `"SDS"`:

xxxxxxxxxx result = nairobi.update_many(    {"metadata.sensor_type": {"$eq": "SDS101"}},    {"$set": {"metadata.sensor_type": "SDS"}},)

xxxxxxxxxxNow we can see all records have changed:

xxxxxxxxxx result = nairobi.find(    {"timestamp": {"$eq": datetime.datetime(2018, 9, 1, 0, 0, 2, 472000)}}).limit(3)​pp.pprint(list(result))

xxxxxxxxxxWe can change it back:

xxxxxxxxxx result = nairobi.update_many(    {"metadata.sensor_type": {"$eq": "SDS"}},    {"$set": {"metadata.sensor_type": "SDS101"}},)

xxxxxxxxxx result.raw_result

xxxxxxxxxx## Aggregation

xxxxxxxxxxSince we're looking for *big* numbers, we need to figure out which one of those dimensions has the largest number of measurements by **aggregating** the data in each document. Since we already know that `site 3` has significantly more documents than `site 2`, let's start looking at `site 3`. We can use the `$match` syntax to only select `site 3` data. The code to do that looks like this:

xxxxxxxxxx result = lagos.aggregate(    [        {"$match": {"metadata.site": 3}},  # `3` is the site number.        {"$group": {"_id": "$metadata.measurement", "count": {"$count": {}}}},    ])pp.pprint(list(result))

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx result = ...pp.pprint(list(result))

xxxxxxxxxxAfter aggregation, there is another useful operator called `$project`, which allows you to specify which fields to display by adding new fields or deleting fields. Using the Nairobi data from site 29, we can first count each sensor type:<span style='color: transparent; font-size:1%'>WQU WorldQuant University Applied Data Science Lab QQQQ</span>

xxxxxxxxxx result = nairobi.aggregate(    [        {"$match": {"metadata.site": 29}},        {"$group": {"_id": "$metadata.sensor_type", "count": {"$count": {}}}},    ])​pp.pprint(list(result))

xxxxxxxxxxWe can see there are two sensor types and the corresponding counts. If we only want to display what are the types but do not care about the counts, we can suppress the `count` filed by setting it at 0 in `$project`:

xxxxxxxxxx result = nairobi.aggregate(    [        {"$match": {"metadata.site": 29}},        {"$group": {"_id": "$metadata.sensor_type", "count": {"$count": {}}}},        {"$project": {"count": 0}},    ])​pp.pprint(list(result))

xxxxxxxxxxThe `$project` syntax is also useful for deleting the intermediate fields that we used to generate our final fields but no longer need. In the following example, let's calculate the date difference for each sensor type. We'll first use the aggregation method to get the start date and last date.

xxxxxxxxxx result = nairobi.aggregate(    [        {"$match": {"metadata.site": 29}},        {            "$group": {                "_id": "$metadata.sensor_type",                "date_min": {"$min": "$timestamp"},                "date_max": {"$max": "$timestamp"},            }        },    ])​pp.pprint(list(result))

xxxxxxxxxxThen we can calculate the date difference using `$dateDiff`, which gets the date difference through specifying the start date, end date and unit for timestamp data. We can see from the results above that the dates, are very close to each other. The only differences are in the minutes, so we can specify the unit as minute to show the difference. Since we don't need the start date and end dates, we can define a `"dateDiff"` field inside `$project`, so that it will be shown in the final display:

xxxxxxxxxx result = nairobi.aggregate(    [        {"$match": {"metadata.site": 29}},        {            "$group": {                "_id": "$metadata.sensor_type",                "date_min": {"$min": "$timestamp"},                "date_max": {"$max": "$timestamp"},            }        },        {            "$project": {                "dateDiff": {                    "$dateDiff": {                        "startDate": "$date_min",                        "endDate": "$date_max",                        "unit": "minute",                    }                }            }        },    ])​pp.pprint(list(result))

xxxxxxxxxxIf we specify unit as `day`, it will show the difference between the dates:

xxxxxxxxxx result = nairobi.aggregate(    [        {"$match": {"metadata.site": 29}},        {            "$group": {                "_id": "$metadata.sensor_type",                "date_min": {"$min": "$timestamp"},                "date_max": {"$max": "$timestamp"},            }        },        {            "$project": {                "dateDiff": {                    "$dateDiff": {                        "startDate": "$date_min",                        "endDate": "$date_max",                        "unit": "day",                    }                }            }        },    ])​pp.pprint(list(result))

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx result = ...​pp.pprint(list(result))

xxxxxxxxxxWe can do more with the date data using `$dateTrunc`, which truncates datetime data. We need to specify the datetime data, which can be a `Date`, a `Timestamp`, or an `ObjectID`. Then we need to specify the `unit` (year, month, day, hour, minute, second) and `binSize` (numerical variable defining the size of the truncation). Let's check the example below, where we group data by the month using `$dateTrunc` and then count how many observations there are for each month.

xxxxxxxxxx result = nairobi.aggregate(    [        {"$match": {"metadata.site": 29}},        {            "$group": {                "_id": {                    "truncatedDate": {                        "$dateTrunc": {                            "date": "$timestamp",                            "unit": "month",                            "binSize": 1,                        }                    }                },                "count": {"$count": {}},            }        },    ])pp.pprint(list(result))

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx result = ...​pp.pprint(list(result))

xxxxxxxxxx## Finishing Up

xxxxxxxxxx result = lagos.find(    {"metadata.site": 3, "metadata.measurement": "P2"},    # `projection` limits the kinds of data we'll get back.    projection={"P2": 1, "timestamp": 1, "_id": 0},)pp.pprint(result.next())

xxxxxxxxxxFinally, we'll use pandas to read the extracted data into a DataFrame, making sure to set `timestamp` as the index:

xxxxxxxxxx df = pd.DataFrame(result).set_index("timestamp")df.head()

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx result = ...df = ...df.head()

xxxxxxxxxx# References & Further Reading

xxxxxxxxxx---

xxxxxxxxxx​

xxxxxxxxxx<font size="+3"><strong>Machine Learning: Core Concepts</strong></font>

xxxxxxxxxx# Model Types

xxxxxxxxxx**Linear regression** is a way to predict the value of some a target variable by fitting a line that best describes the relationship between **Big X** and **little y** for the values we already have. If you remember `y = mx + b` from Algebra, it's the same thing; the `y` is the intercept, and the `b` is the beta coefficient. The beta coefficient tells us what change we can expect to see in `X` for every one-unit increase in `y`. If that doesn't seem familiar to you, don't worry about it; we'll give you everything you need to know.

xxxxxxxxxx# Statistical Concepts

xxxxxxxxxx## Cost Functions

xxxxxxxxxx## Residuals

xxxxxxxxxx## Performance Metrics

xxxxxxxxxx# Data Concepts

xxxxxxxxxx## Leakage

xxxxxxxxxx## Imputation

xxxxxxxxxx## Generalization

xxxxxxxxxx# Model Concepts

xxxxxxxxxx## Hyperparameters

xxxxxxxxxxWhen we instantiate an estimator, we can pass keyword arguments that will dictate its structure. These arguments are called **hyperparameters**. For example, when we defined our decision tree estimator, we chose how many layers the tree would have using the `max_depth` keyword. This is in contrast to **parameters**, which are the numbers that our model uses to make predictions based on features. Parameters are optimized during the training process based on data and input features. They keep changing during training to fit the data and only the best performed ones were selected.  Hyperparameters values are set before training begins and will not be changed during the training process. Pretty much all models have hyperparameters. Even a simple linear regressor has a hyperparameter: `fit_intercept`. Here are some common examples for Hyperparameters:

xxxxxxxxxx# References and Further Reading

xxxxxxxxxx- [Parameters and Hyperparameters in Machine Learning and Deep Learning](https://towardsdatascience.com/parameters-and-hyperparameters-aa609601a9ac)

xxxxxxxxxx---

xxxxxxxxxx---

xxxxxxxxxx​

xxxxxxxxxx<font size="+3"><strong>Machine Learning: Data Pre-Processing and Production</strong></font>

 import warnings​warnings.simplefilter(action="ignore", category=FutureWarning)

xxxxxxxxxx# What's scikit-learn?

xxxxxxxxxx# Data Preprocessing

xxxxxxxxxx# Standardization

xxxxxxxxxx**Standardization** is a widely used scaling technique to transform features before fitting into models. Feature scaling changes all a dataset's continuous features to give us a more consistent range of values. Specifically, we subtract the mean from each data point and then divide by the standard deviation:

xxxxxxxxxxLet's check the following example where we apply standardization on one of the columns in the following DataFrame:

 import pandas as pd​# Read CSV into DataFramedf = pd.read_csv("./data/mexico-city-test-features.csv").dropna()​df.head()

xxxxxxxxxxOur target feature is the `"surface_covered_in_m2"` column. Let's first check the maximum and minimum of this column before standardization:

 print("Maximum before standardization is:", df["surface_covered_in_m2"].max())print("Minimum before standardization is:", df["surface_covered_in_m2"].min())

Maximum before standardization is: 280.0
Minimum before standardization is: 50.0

xxxxxxxxxxWe can perform the transformation by first instantiating the scaler and assigning the feature to a variable name. Then we fit the scaler and transform the data:

 from sklearn.preprocessing import StandardScaler​# Name the scaler and targeted featuresscaler = StandardScaler()X_train = df[["surface_covered_in_m2"]]

 # Fit the scaler to featurescaler.fit(X_train)

StandardScaler()

StandardScaler()

 # Pass the scaler to feature to transform dataX_transformed = scaler.transform(X_train)X_transformed

array([[ 1.62304525e-01],
       [-1.11902808e+00],
       [ 6.24863439e+00],
       ...,
       [ 2.13794980e-03],
       [-3.18195201e-01],
       [ 2.13794980e-03]])

xxxxxxxxxxNow you can see the transformed data range is much smaller after standardization:

 print("Maximum after standardization is:", X_transformed.max())print("Minimum after standardization is:", X_transformed.min())

Maximum after standardization is: 6.248634385593622
Minimum after standardization is: -1.1190280771385155

xxxxxxxxxxWe can also combine the fit and transform process together into one step:

 X_transformed = scaler.fit_transform(X_train)X_transformed

array([[ 1.62304525e-01],
       [-1.11902808e+00],
       [ 6.24863439e+00],
       ...,
       [ 2.13794980e-03],
       [-3.18195201e-01],
       [ 2.13794980e-03]])

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx df1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")df1.head()

xxxxxxxxxx scaler = ...X_train = ...X_transformed = ...X_transformed

Ellipsis

xxxxxxxxxx## One-Hot Encoding

xxxxxxxxxxA property's district is **categorical data**, or data which can be divided into groups.  For many machine learning algorithms, it's common to create a column in a DataFrame to indicate if the feature is present or absent, instead of using the category's name. First you a column for each district names then, for each observation, you put a 1 or a 0 to indicate if the property is located in each neighborhood or not. Let's take a look at the `mexico-city-test-features.csv` dataset for properties which include the district.

xxxxxxxxxx import pandas as pd​# Read CSV into DataFramedf = pd.read_csv("./data/mexico-city-test-features.csv").dropna()​df.head()

xxxxxxxxxxYou can do one-hot encoding using pandas [`get_dummies`](https://pandas.pydata.org/docs/reference/api/pandas.get_dummies.html) function, but we'll use a the [Category Encoders](https://contrib.scikit-learn.org/category_encoders/) library since it allows us to integrate the one hot encoder as a transformer in a scikit-learn Pipeline.

xxxxxxxxxx from category_encoders import OneHotEncoder​# Instantiate transformerohe = OneHotEncoder(use_cat_names=True)​# Fit transformer to dataohe.fit(df)​# Transform datadf_ohe = ohe.transform(df)​df_ohe.head()

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx mexico_city1 = pd.read_csv(    "./data/mexico-city-real-estate-1.csv", usecols=["property_type"])ohe = ...mexico_city1_ohe = ...mexico_city1_ohe.head()

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In [13], line 6
      4 ohe = ...
      5 mexico_city1_ohe = ...
----> 6 mexico_city1_ohe.head()

AttributeError: 'ellipsis' object has no attribute 'head'

xxxxxxxxxx## Ordinal Encoding

xxxxxxxxxxFor many machine learning algorithms, it's common to use one-hot encoding. This works well if there are a few categories, but as the number of features grows, the number of additional columns also grows.

xxxxxxxxxx from category_encoders import OrdinalEncoder​# Instantiate transformeroe = OrdinalEncoder()​# Fit transformer to dataoe.fit(df)​# Transform dataX_train_oe = oe.transform(df)​X_train_oe.head()

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx mexico_city1 = pd.read_csv(    "./data/mexico-city-real-estate-1.csv", usecols=["property_type"])​oe = ...mexico_city1_oe = ...mexico_city1_oe.head()

xxxxxxxxxx## Imputation

xxxxxxxxxxLet's take a look at `mexico-city-real-estate-1.csv` and impute some of the missing values. First, we'll load the dataset, limiting ourselves to the `"surface_covered_in_m2"` and `"price_aprox_usd"` columns.

xxxxxxxxxx columns = ["surface_covered_in_m2", "price_aprox_usd"]mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv", usecols=columns)mexico_city1.info()

xxxxxxxxxxWhen you need to build a model using features that contain missing values, one helpful tool is the scikit-learn transformer [`SimpleImputer`](https://scikit-learn.org/stable/modules/generated/sklearn.impute.SimpleImputer.html). In order to use it, we need to start by instantiating the transformer.

xxxxxxxxxx from sklearn.impute import SimpleImputer​imputer = SimpleImputer()

xxxxxxxxxxNext, we train the imputer using the data. At this step it will calculate the mean value for each column.

xxxxxxxxxx imputer.fit(mexico_city1)

xxxxxxxxxxLast, we transform the data using the imputer.

xxxxxxxxxx mexico_city1_imputed = imputer.transform(mexico_city1)

xxxxxxxxxxSince the imputer doesn't return a DataFrame, let's transform it into one.

xxxxxxxxxx mexico_city1_imputed = pd.DataFrame(mexico_city1_imputed, columns=columns)mexico_city1_imputed.info()

xxxxxxxxxxNow there are no missing values!

xxxxxxxxxxThen we use the imputer to transform the data.

xxxxxxxxxx # Import datacolumns = ["surface_covered_in_m2", "price_aprox_usd"]mexico_city2 = ...​# Instantiate transformerimputer = ...​# Fit transformer to data​​# Transform datamexico_city2_imputed = ...​# Create DataFramemexico_city2_imputed = pd.DataFrame(mexico_city2_imputed, columns=columns)​mexico_city2_imputed.info()

xxxxxxxxxx## Data Leakage

xxxxxxxxxxLet's consider the `mexico-city-real-estate-1.csv` dataset and fit a regression model using `surface_covered_in_m2` and `price_aprox_local_currency` to estimate `price_aprox_usd`.

xxxxxxxxxx import pandas as pdfrom sklearn.linear_model import LinearRegressionfrom sklearn.metrics import mean_absolute_error​# Import datacolumns = [    "price",    "price_aprox_local_currency",    "price_aprox_usd",    "surface_total_in_m2",    "surface_covered_in_m2",    "price_per_m2",]​mexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv", usecols=columns)​# Drop rows with missing valuesmexico_city1.dropna(inplace=True)​lr = LinearRegression()lr.fit(    mexico_city1[["surface_covered_in_m2", "price_aprox_local_currency"]],    mexico_city1["price_aprox_usd"],)

xxxxxxxxxxNow let's calculate the mean absolute error in our training data.

xxxxxxxxxx price_pred = lr.predict(    mexico_city1[["surface_covered_in_m2", "price_aprox_local_currency"]])mean_absolute_error(price_pred, mexico_city1["price_aprox_usd"])

xxxxxxxxxxWhen you see a mean absolute error that's so close to zero (especially when the mean apartment price is so much larger), chances are there is leakage in your model!

xxxxxxxxxx# Imbalanced Data

xxxxxxxxxxWhen dealing with classification problems, we would ideally expect the training data to be evenly spread across different classes for better model performance. When the numbers of observations are uneven in different classes, we have imbalanced data. The class that represents the majority of observations is called the **majority class**, while the class with limited observation is called the **minority class**. Imbalanced data limits training data available for certain classes. In addition, when the one class takes the majority of the data, the model will keep predicting the majority class to achieve high accuracy result. Thus, prior to training a  model, it is essential to balance the data either through under-sampling the majority classes, or over-sampling the minority classes, or use other evaluation metrics like **recall** or **precision**.

xxxxxxxxxx## Under-sampling

xxxxxxxxxxWhen data is imbalanced in different classes, one way we can balance it is reducing the number of observations in the majority class. This is called **under-sampling**. We can under-sample by randomly deleting some observations in the majority class. The open source [imbalanced-learn](https://imbalanced-learn.org/stable/) (imported as `imblearn`) is an open-source library that works with `scikit-learn` and provides tools when dealing with imbalanced classes. Here's an example of randomly deleting observations from the majority class using Poland bankruptcy data from 2008.

xxxxxxxxxx import gzipimport json​with gzip.open("data/poland-bankruptcy-data-2008.json.gz", "r") as f:    poland_data_gz = json.load(f)​df = pd.DataFrame().from_dict(poland_data_gz["data"])​df["bankrupt"].value_counts()

xxxxxxxxxxThe data is clearly imbalanced as there are many more observations in non-bankruptcy compared to bankruptcy.

xxxxxxxxxx from imblearn.under_sampling import RandomUnderSampler​X, y = RandomUnderSampler().fit_resample(df[["company_id"]], df[["bankrupt"]])y["bankrupt"].value_counts()

xxxxxxxxxxNow we have reduced the non-bankruptcy class to the same size as the bankruptcy class.

xxxxxxxxxx## Over-sampling

xxxxxxxxxx**Over-sampling** is the opposite of under-sampling. Instead of reducing the majority class, over-sampling increases the number of observations in the minority class by randomly making copies of the existing observations. Here is an example of making random copies from the minority class using the Poland bankruptcy data and `imblearn`.

xxxxxxxxxx from imblearn.over_sampling import RandomOverSampler​X, y = RandomOverSampler().fit_resample(df[["company_id"]], df[["bankrupt"]])y["bankrupt"].value_counts()

xxxxxxxxxxNow we have increased the bankruptcy class to the size of the non-bankruptcy class.

xxxxxxxxxx### Practice

xxxxxxxxxxNow that you've seen an example of imbalanced data and how to under-  or over-sample it prior to model training, let's get some practice with the Poland bankruptcy data from 2007.

xxxxxxxxxx with gzip.open("data/poland-bankruptcy-data-2007.json.gz", "r") as f:    poland_data_gz_2007 = json.load(f)​df_2007 = pd.DataFrame().from_dict(poland_data_gz_2007["data"])

xxxxxxxxxxFirst, check whether this data is imbalanced.

xxxxxxxxxx ​

xxxxxxxxxxNext, do under-sampling.

xxxxxxxxxx X, y = ...

xxxxxxxxxxFinally, check whether the data is balanced.

xxxxxxxxxx ​

xxxxxxxxxxGreat work! Now try over-sampling.

xxxxxxxxxx X, y = ...

xxxxxxxxxxAnd check whether the data is balanced.

xxxxxxxxxx ​

xxxxxxxxxx# scikit-learn in Production

xxxxxxxxxxThe previous examples have built models and made predictions one step at a time.  Many machine learning applications will require you to run the same steps many times, usually with new or updated data.  scikit-learn allows you to define a set of steps to process data for machine learning in a reproducible manner using a pipeline.

xxxxxxxxxx## Creating a Pipeline in scikit-learn

xxxxxxxxxxFirst, we create a pipeline to do linear regression on the transformed data set.

xxxxxxxxxx import pandas as pdfrom sklearn import linear_modelfrom sklearn.pipeline import Pipeline​# construct pipelinelin_reg = linear_model.LinearRegression()​pipe = Pipeline([("regressor", lin_reg)])

xxxxxxxxxxWe can check the steps in the pipeline, but right now, there's only 1.

xxxxxxxxxx pipe.named_steps

xxxxxxxxxxThen we fit a linear regression model to our data.

xxxxxxxxxx # fit/train model and predict labelsmexico_city1 = pd.read_csv("./data/mexico-city-real-estate-1.csv")mexico_city1 = mexico_city1.drop(    [        "floor",        "price_usd_per_m2",        "expenses",        "rooms",        "price_per_m2",        "price",        "surface_total_in_m2",    ],    axis=1,)mexico_city1 = mexico_city1.dropna(axis=0)mexico_city1["surface_covered_in_m2"] = mexico_city1["surface_covered_in_m2"].astype(    float)​y = mexico_city1["price_aprox_usd"]X = mexico_city1.surface_covered_in_m2.values.reshape(-1, 1)pipe.fit(X, y)y_pred = pd.DataFrame(pipe.predict(X))

xxxxxxxxxx print(y_pred.head())

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx y = ...X = ...pip.fit(...,...)y_pred = ...print(y_pred.head())

xxxxxxxxxxLet's use the `make_pipeline` function to create a pipeline to fit a linear regression model for the `mexico-city-real-estate-1.csv` dataset.

xxxxxxxxxx from sklearn.pipeline import make_pipeline​y = mexico_city1["price_aprox_usd"]X = mexico_city1.surface_covered_in_m2.values.reshape(-1, 1)model_lr = make_pipeline(linear_model.LinearRegression())model_lr.fit(X, y)

xxxxxxxxxxLet's try to predict `price_aprox_usd` in the `mexico-city-test-features.csv` dataset.

xxxxxxxxxx mexico_city_features = pd.read_csv("./data/mexico-city-test-features.csv")mexico_city_labels = pd.read_csv("./data/mexico-city-test-labels.csv")X = mexico_city_features.surface_covered_in_m2.values.reshape(-1, 1)model_lr.predict(X)

xxxxxxxxxx## Accessing an Object in a Pipeline

xxxxxxxxxxLet's figure out the regression coefficients.

xxxxxxxxxx pipe.named_steps["regressor"].coef_

xxxxxxxxxx<font size="+1">Practice</font>

xxxxxxxxxx ​# INCLUDE pipe.named_steps[...].intercept_

xxxxxxxxxx*References & Further Reading*

xxxxxxxxxx---

	operation	property_type	place_with_parent_names	lat-lon	price	currency	price_aprox_local_currency	price_aprox_usd	surface_total_in_m2	surface_covered_in_m2	price_per_m2	properati_url
2	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	2700000.0	MXN	2748947.10	146154.51	61.0	61.0	44262.295082	http://cuauhtemoc.properati.com.mx/2pu_venta_a...
3	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	6347000.0	MXN	6462061.92	343571.36	176.0	128.0	49585.937500	http://cuauhtemoc.properati.com.mx/2pv_venta_a...
6	sell	apartment	\|México\|Distrito Federal\|Miguel Hidalgo\|	19.456564,-99.191724	670000.0	MXN	682146.11	36267.97	65.0	65.0	10307.692308	http://miguel-hidalgo-df.properati.com.mx/46h_...
7	sell	apartment	\|México\|Distrito Federal\|Gustavo A. Madero\|	19.512787,-99.141393	1400000.0	MXN	1425379.97	75783.82	82.0	70.0	20000.000000	http://gustavo-a-madero.properati.com.mx/46p_v...
8	sell	house	\|México\|Distrito Federal\|Álvaro Obregón\|	19.358776,-99.213557	6680000.0	MXN	6801098.67	361597.08	346.0	346.0	19306.358382	http://alvaro-obregon.properati.com.mx/46t_ven...

	operation	property_type	place_with_parent_names	lat-lon	price	currency	price_aprox_local_currency	price_aprox_usd	surface_total_in_m2	surface_covered_in_m2	price_per_m2	properati_url
2	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	2700000.0	MXN	2748947.10	146154.51	61.0	61.0	44262.295082	http://cuauhtemoc.properati.com.mx/2pu_venta_a...
3	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	6347000.0	MXN	6462061.92	343571.36	176.0	128.0	49585.937500	http://cuauhtemoc.properati.com.mx/2pv_venta_a...
6	sell	apartment	\|México\|Distrito Federal\|Miguel Hidalgo\|	19.456564,-99.191724	670000.0	MXN	682146.11	36267.97	65.0	65.0	10307.692308	http://miguel-hidalgo-df.properati.com.mx/46h_...
7	sell	apartment	\|México\|Distrito Federal\|Gustavo A. Madero\|	19.512787,-99.141393	1400000.0	MXN	1425379.97	75783.82	82.0	70.0	20000.000000	http://gustavo-a-madero.properati.com.mx/46p_v...
8	sell	house	\|México\|Distrito Federal\|Álvaro Obregón\|	19.358776,-99.213557	6680000.0	MXN	6801098.67	361597.08	346.0	346.0	19306.358382	http://alvaro-obregon.properati.com.mx/46t_ven...

	operation	property_type	place_with_parent_names	lat-lon	price	currency	price_aprox_local_currency	price_aprox_usd	surface_total_in_m2	surface_covered_in_m2	price_per_m2	properati_url
2	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	2700000.0	MXN	2748947.10	146154.51	61.0	61.0	44262.295082	http://cuauhtemoc.properati.com.mx/2pu_venta_a...
3	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	6347000.0	MXN	6462061.92	343571.36	176.0	128.0	49585.937500	http://cuauhtemoc.properati.com.mx/2pv_venta_a...
6	sell	apartment	\|México\|Distrito Federal\|Miguel Hidalgo\|	19.456564,-99.191724	670000.0	MXN	682146.11	36267.97	65.0	65.0	10307.692308	http://miguel-hidalgo-df.properati.com.mx/46h_...
7	sell	apartment	\|México\|Distrito Federal\|Gustavo A. Madero\|	19.512787,-99.141393	1400000.0	MXN	1425379.97	75783.82	82.0	70.0	20000.000000	http://gustavo-a-madero.properati.com.mx/46p_v...
8	sell	house	\|México\|Distrito Federal\|Álvaro Obregón\|	19.358776,-99.213557	6680000.0	MXN	6801098.67	361597.08	346.0	346.0	19306.358382	http://alvaro-obregon.properati.com.mx/46t_ven...

	operation	property_type	place_with_parent_names	lat-lon	price	currency	price_aprox_local_currency	price_aprox_usd	surface_total_in_m2	surface_covered_in_m2	price_per_m2	properati_url	Country	City	Borough
2	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	2700000.0	MXN	2748947.10	146154.51	61.0	61.0	44262.295082	http://cuauhtemoc.properati.com.mx/2pu_venta_a...	México	Distrito Federal	Cuauhtémoc
3	sell	apartment	\|México\|Distrito Federal\|Cuauhtémoc\|	19.41501,-99.175174	6347000.0	MXN	6462061.92	343571.36	176.0	128.0	49585.937500	http://cuauhtemoc.properati.com.mx/2pv_venta_a...	México	Distrito Federal	Cuauhtémoc
6	sell	apartment	\|México\|Distrito Federal\|Miguel Hidalgo\|	19.456564,-99.191724	670000.0	MXN	682146.11	36267.97	65.0	65.0	10307.692308	http://miguel-hidalgo-df.properati.com.mx/46h_...	México	Distrito Federal	Miguel Hidalgo
7	sell	apartment	\|México\|Distrito Federal\|Gustavo A. Madero\|	19.512787,-99.141393	1400000.0	MXN	1425379.97	75783.82	82.0	70.0	20000.000000	http://gustavo-a-madero.properati.com.mx/46p_v...	México	Distrito Federal	Gustavo A. Madero
8	sell	house	\|México\|Distrito Federal\|Álvaro Obregón\|	19.358776,-99.213557	6680000.0	MXN	6801098.67	361597.08	346.0	346.0	19306.358382	http://alvaro-obregon.properati.com.mx/46t_ven...	México	Distrito Federal	Álvaro Obregón

Magic Command	Description of Command
`%pwd`	Print the current working directory
`%cd`	Change the current working directory
`%ls`	List the contents of the current directory
`%history`	Show the history of the In [ ]: commands

	index	Borough
0	Miguel Hidalgo	345
1	Cuajimalpa de Morelos	255
2	Álvaro Obregón	203
3	Benito Juárez	198
4	Tlalpan	171
5	Iztapalapa	134
6	Tláhuac	125
7	Cuauhtémoc	120
8	Gustavo A. Madero	89
9	Venustiano Carranza	81
10	Coyoacán	80
11	La Magdalena Contreras	41
12	Xochimilco	34
13	Iztacalco	27
14	Azcapotzalco	24
15	Milpa Alta	1

Clause Name	Definition
`SELECT`	Determines which columns to include in the query's result
`FROM`	Identifies the table from which to query the data from
`WHERE`	filters data
`GROUP BY`	groups rows by common values in columns
`HAVING`	filters out unwanted groups from GROUP BY
`ORDER BY`	Orders the rows using one or more columns
`LIMIT`	Outputs the specified number of rows

Comparison Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	String comparison test

Aggregation Function	Definition
`MIN`	Return the minimum value
`MAX`	Return the largest value
`SUM`	Return the sum of values
`AVG`	Return the average of values
`COUNT`	Return the number of observations

JOIN Type	Definition
`INNER JOIN`	Returns rows where ID is in both tables
`LEFT JOIN`	Returns rows where ID is in the left table. Return NA for values in column, if ID is not in right table.
`RIGHT JOIN`	Returns rows where ID is in the right table. Return NA for values in column, if ID is not in left table.
`FULL JOIN`	Returns rows where ID is in either table. Return NA for values in column, if ID is not in either table.
WQU WorldQuant University Applied Data Science Lab QQQQ

	surface_covered_in_m2	lat	lon	neighborhood
0	90.0	19.367931	-99.170262	Benito Juárez
1	50.0	19.363542	-99.224084	Álvaro Obregón
2	280.0	19.457982	-99.192690	Miguel Hidalgo
3	55.0	19.334270	-99.083374	Iztapalapa
4	80.0	19.416881	-99.109781	Venustiano Carranza

	State	Temparature
0	CA	100
1	TX	120
2	NY	110
3	HI	90
4	DE	105

Open Tabs

Kernels

Terminals

Scatter Plots¶

3D Scatter Plots¶

Mapbox Scatter Plots¶

Choropleth Maps¶

Histogram¶

Boxplots¶

Bar Chart¶

References and Further Reading¶

Scatter Plots¶

Bar Charts¶

Correlation Heatmaps¶

References and Further Reading¶

Working with SQL Databases¶

Connecting to a Database¶

ipython-sql¶

Magic Commands¶

ipython-sql¶

Connecting with ipython-sql¶

sqlite3¶

Querying a Database¶

Building Blocks of the Basic Query¶

SELECT and FROM¶

Filtering and Sorting Data¶

Aggregating Data¶

Joining Tables¶

Using pandas with SQL Databases¶

References & Further Reading¶

Working with PyMongo¶

Databases¶

Servers and Clients¶

Semi-structured Data¶

Exploring a Database¶

Collections¶

Documents¶

Retrieving Data¶

Key-Value Pairs¶

Metadata¶

Identifiers¶

Analyzing Data¶

Updating Documents¶

Aggregation¶

Finishing Up¶

References & Further Reading¶

Model Types¶

Statistical Concepts¶

Cost Functions¶

Residuals¶

Performance Metrics¶

Data Concepts¶

Leakage¶

Imputation¶

Generalization¶

Model Concepts¶

Hyperparameters¶

References and Further Reading¶

What's scikit-learn?¶

Data Preprocessing¶

Standardization¶

One-Hot Encoding¶

Ordinal Encoding¶

Imputation¶

Data Leakage¶

Imbalanced Data¶

Under-sampling¶

Over-sampling¶

Practice¶

scikit-learn in Production¶

Creating a Pipeline in scikit-learn¶

Accessing an Object in a Pipeline¶

-

Variables

Callstack

Breakpoints

Source